package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.Apartment;

public class DBApartment implements IFDBApartment{
	
	private Connection con;
	private static DBApartment dbApart=null;
	
	private DBApartment(){
		con = DBConnection.getInstance().getDBcon();
	}
	
	public static DBApartment getInstance(){
		if(dbApart==null) dbApart = new DBApartment();
		return dbApart;
	}
	
	
	@Override
	public int getPropertiesWithoutTenants(){
		String query = "SELECT COUNT(*) FROM Apartment ap WHERE ap.apartId NOT IN (SELECT apartId FROM Contract WHERE dateadd(mm,period,startDate)>getdate())";
		
		return countQuery(query);
	}
	
	@Override
	public int getProperties(){
		String query = "SELECT COUNT(*) FROM Apartment";
		
		return countQuery(query);
	}
	
	
	private int countQuery(String query)
	{
		int count=0;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			ResultSet rs = stmt.executeQuery(query);
			if(rs.next()) count = rs.getInt(1);
		}
		catch(Exception ex){
			ex.printStackTrace();
		}
		
		return count;
	}
	
	@Override
	public ArrayList<Apartment> getAll() {
		return miscWhere("");
	}
	
	@Override
	public ArrayList<Apartment> getAll_withAutoLevy() {
		return miscWhere("autoLevy='true'");
	}
	
	@Override
	public ArrayList<Apartment> getAll_withAutoRate() {
		return miscWhere("autoRate='true'");
	}
	
	@Override
	public ArrayList<Apartment> getApartmentsByLandlordId(int landId){
		return miscWhere("landLordId="+landId);
	}
	
	@Override
	public ArrayList<Apartment> getAll_where(String wClause) {
		return miscWhere(wClause);
	}
	
	@Override
	public ArrayList<Apartment> searchRandom(String searchF) {
		
		return miscWhere("apartId LIKE '"+searchF+"' OR apartNo LIKE '"+searchF+"' OR monthlyRent LIKE '"+searchF+"' OR ap.noOfRooms LIKE '"+searchF+"' OR size LIKE '"+searchF+"' OR deposit LIKE '"+searchF+"' OR landLordId LIKE '"+searchF+"' OR b.buildingNr LIKE '"+searchF+"' OR b.buildingName LIKE '"+searchF+"' OR fname LIKE '"+searchF+"' OR lname LIKE '"+searchF+"'");
	}
	
	@Override
	public Apartment find(int apartId, boolean getContracts) {
		return singleWhere("apartId="+apartId, getContracts);
	}
	
	public void insertLevyAndRates(Apartment apart){
		
		DBLevyRate.getInstance().insertRateRooms(apart.getNoOfRooms(), apart.getRateRooms());
		DBLevyRate.getInstance().insertLevy(apart.getNoOfRooms(), apart.getLevyAmount(), apart.getBuilding().getBuidlingNr());
		DBLevyRate.getInstance().insertRateZone(apart.getBuilding().getBuidlingNr(), apart.getRateZone());
		
	}

	@Override
	public int insert(Apartment apartment) {
		int rc = -1;
		String query = "INSERT INTO Apartment (apartNo,monthlyRent,noOfRooms,size,parkingBay,deposit,autoLevy,autoRate,landLordId,buildingNr) "
				     + "VALUES ("+apartment.getApartNo()+","+apartment.getMonthlyRent()+","+apartment.getNoOfRooms()+","+apartment.getSize()+",'"+apartment.isParkingBay()+"',"+apartment.getDeposit()+",'"+apartment.isAutoLevy()+"','"+apartment.isAutoRate()+"',"+apartment.getLandlord().getPid()+","+apartment.getBuilding().getBuidlingNr()+")";
		
		try{
			insertLevyAndRates(apartment);
			
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the Apartment "+e.getMessage());
		}
		
		return rc;
	}

	@Override
	public int update(Apartment apartment) {
		int rc = -1;
		String query = "UPDATE Apartment SET apartNo="+apartment.getApartNo()+",monthlyRent="+apartment.getMonthlyRent()+",noOfRooms="+apartment.getNoOfRooms()+",size="+apartment.getSize()+",parkingBay='"+apartment.isParkingBay()+"',deposit="+apartment.getDeposit()+",autoLevy='"+apartment.isAutoLevy()+"',autoRate='"+apartment.isAutoRate()+"',landLordId="+apartment.getLandlord().getPid()+",buildingNr="+apartment.getBuilding().getBuidlingNr()+" WHERE apartId="+apartment.getApartId();
		
		try{
			insertLevyAndRates(apartment);
			
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not update the Apartment "+e.getMessage());
		}
		
		return rc;
	}

	@Override
	public int delete(int apartId) {
		int rc = -1;
		String query = "DELETE FROM Apartment WHERE apartId="+apartId;
		
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not delete the Apartment "+e.getMessage());
		}
		
		return rc;
	}
	
	
	
	
	
	private Apartment singleWhere(String wClause, boolean getContracts){
		Apartment apartment = new Apartment();
		
		ResultSet result;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				apartment = buildApartment(result,getContracts);
			}
			
			stmt.close();
			
		}
		catch(Exception e){
			System.err.println("Could not execute select Apartment query "+e.getMessage());
		}
		
		return apartment;
	}
	
	
	private ArrayList<Apartment> miscWhere(String wClause){
		ArrayList<Apartment> apartments = new ArrayList<>();
		
		ResultSet results;
		String query = buildQuery(wClause);
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				apartments.add(buildApartment(results,true));
			}
			
			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not execute multiple select Apartment query "+e.getMessage());
		}
		
		return apartments;
	}
	
	
	private String buildQuery(String wClause){
		String query = "SELECT apartId,apartNo,monthlyRent,ap.noOfRooms,size,parkingBay, "
					 + "deposit,autoLevy,autoRate,landLordId,b.buildingNr,levyAmount,rateAmount,rateZoneAmount "
					 + "FROM Apartment ap "
					 + "LEFT JOIN Building b ON b.buildingNr = ap.buildingNr "
					 + "LEFT JOIN RateValues rv ON ap.noOfRooms=rv.noOfRooms  "
					 + "LEFT JOIN LevyValues lv ON (ap.noOfRooms=lv.noOfRooms AND lv.zone=b.zone) "
					 + "LEFT JOIN People p ON landLordId=p.pid "
					 + "LEFT JOIN RateZoneValue rzv ON lv.zone=rzv.zone ";
		
		if(wClause.length()>0) query += " WHERE "+wClause;
		
		return query;
	}
	
	
	
	private Apartment buildApartment(ResultSet result, boolean getContracts){
		Apartment apartment = new Apartment();
		
		try{
			apartment.setApartId(result.getInt("apartId"));
			apartment.setApartNo(result.getInt("apartNo"));
			apartment.setMonthlyRent(result.getDouble("monthlyRent"));
			apartment.setNoOfRooms(result.getInt("noOfRooms"));
			apartment.setSize(result.getDouble("size"));
			apartment.setParkingBay(result.getBoolean("parkingBay"));
			apartment.setDeposit(result.getDouble("deposit"));
			apartment.setAutoLevy(result.getBoolean("autoLevy"));
			apartment.setAutoRate(result.getBoolean("autoRate"));
			apartment.setLevyAmount(result.getDouble("levyAmount"));
			apartment.setRateRooms(result.getDouble("rateAmount"));
			apartment.setRateZone(result.getDouble("rateZoneAmount"));
			apartment.setLandlord(DBLandlord.getInstance().getLandlordById(result.getInt("landLordId"),false));
			apartment.setBuilding(DBBuilding.getInstance().getBuildingById(result.getInt("buildingNr")));
			apartment.setComments(DBComment.getInstance().getComments_byID(apartment.getApartId(), false));
			if(getContracts) apartment.setContracts(DBContract.getInstance().getContract_byApartmentId(apartment.getApartId()));
		}
		catch(Exception e){
			System.err.println("Could not build the Apartment Result "+e.getMessage());
		}
		
		return apartment;
	}
	
	
	
}
